# coding=utf-8
from openpyxl.styles import Font, Border, Side, Alignment, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl import Workbook
class BaseExcel:
    """
    提供了对excel工作表进行 基础样式设置、基础数据写入、自动列宽计算和标题列筛选 的功能
    """

    def __init__(self, ws, auto_width=True):
        self.ws = ws  # 工作表对象
        self.__auto_width = auto_width  # 自动列宽
        self.__width_list = []  # 列宽列表
        self.__head = None  # 合并单元格的头，类型为字符串，样式为16号字、加粗、居中、黑色实心边框
        self.__title = []  # 标题列数据，类型为列表，样式为黑体、居中、黑色实心边框
        self.__data = []  # 正文数据，类型为二维列表，样式为居中、黑色实心边框
        self.__result = []  # 结果列，类型为二维列表，样式为黄色底纹、居中、黑色实心边框
        self.__alignment = Alignment(horizontal='center', vertical='center')  # 水平居中、垂直居中
        self.__side = Side(style='thin', color='000000')  # 黑色实线边框
        self.__border = Border(bottom=self.__side, right=self.__side, left=self.__side, top=self.__side)
        self.__result_fill = PatternFill(fill_type='solid', start_color='FFFF00')  # 黄色底纹

    @property
    def auto_width(self, auto_width):
        return self.__auto_width

    @auto_width.setter
    def auto_width(self, value):
        self.__auto_width = value

    @property
    def head(self):
        return self.__head

    @head.setter
    def head(self,value):
        self.__head = value

    @property
    def title(self):
        return self.__title

    @title.setter
    def title(self, value):
        self.__title = value
        self.__width__handle(value, init=True)

    def append(self, value, result=False):
        """
        添加单行数据
        :param value:
        :param result: 是否属于结果列
        :return: self
        """
        if result:
            self.__result.append(value)
        else:
            self.__data.append(value)
        self.__width__handle(value)
        return self

    @property
    def data(self):
        return self.__data

    @data.setter
    def data(self, value):
        for item in value:
            self.append(item)
        return self

    @property
    def result(self):
        return self.__result

    @result.setter
    def result(self, value):
        for item in value:
            self.append(item,result=True)
        return self

    def __width__handle(self, value, init=False):
        """
        宽度处理
        :param value:写入的数据
        :param init: 是否初始化，设置标题要进行初始化操作
        :return:
        """
        if self.__auto_width:
            if init:
                for item in value:
                    self.__width_list.append(self.__get_width(item))  # 初始化
            else:
                for index, item in enumerate(value):
                    width = self.__get_width(item)
                    if self.__width_list[index] < width:  # 通过比较获得最大列宽
                        self.__width_list[index] = width

    def __get_width(self, value, min_width=9, max_width=50):
        """
        得到列宽
        计算方式：利用GBK编码方式，非汉字字符占1个长度，汉字字符占2个长度
        :param value:
        :param min_width: 最小列宽
        :param max_width: 最大列宽
        :return:
        """
        width = len(str(value).encode("GBK"))+1        # +1既可以补充误差又可以让两边留有一定的空白，美观
        if width < min_width:
            width = min_width
        if width > max_width:
            width = max_width
        return width

    def execute(self):
        """
        执行将数据按照指定样式写入到工作表操作
        * 最终方法，必须执行且必须在最后执行的方法
        :return:
        """
        if len(self.__title) == 0:
            raise Exception("标题列未设置")
        next_row = 1  # 行的游标
        """设置合并单元格的头"""
        if self.__head is not None:
            head_range = 'A1:{}1'.format(get_column_letter(len(self.__title)))
            head_cell = self.ws[head_range][0]
            for item in head_cell:
                item.border = self.__border
            self.ws.merge_cells(head_range)
            a1 = self.ws["A1"]
            a1.value = self.head
            a1.font = Font(size=16, bold=True)
            a1.alignment = self.__alignment
            next_row += 1
        """设置标题"""
        auto_filter_range = "A{}:{}{}".format(next_row, get_column_letter(len(self.__title)), next_row)
        self.ws.auto_filter.ref = auto_filter_range  # 设置标题行筛选
        for index, item in enumerate(self.__title):
            cell = self.ws.cell(next_row, index + 1)
            cell.value = item
            cell.font = Font(name="黑体", size=12)
            cell.border = self.__border
            cell.alignment = self.__alignment
        next_row += 1
        """设置正文数据"""
        for row in self.__data:
            for index, item in enumerate(row):
                cell = self.ws.cell(next_row, index + 1)
                cell.value = item
                cell.border = self.__border
                cell.alignment = self.__alignment
            next_row += 1
        """设置结果数据"""
        for row in self.__result:
            for index, item in enumerate(row):
                cell = self.ws.cell(next_row, index + 1)
                cell.value = item
                cell.border = self.__border
                cell.alignment = self.__alignment
                cell.fill = self.__result_fill
            next_row += 1
        """设置列宽"""
        if self.__auto_width:
            for index, item in enumerate(self.__width_list):
                self.ws.column_dimensions[get_column_letter(index + 1)].width = item

    def __str__(self):
        value = ""
        if self.head is not None:
            value = value + self.head + "\n"
        value = value + str(self.title) + "\n"
        for item in self.data:
            value = value + str(item) + "\n"
        if len(self.result) > 0:
            for item in self.result:
                value = value + str(item) + "\n"
        return value


"""测试代码"""
if __name__ == "__main__":
    wb = Workbook()  # 新建工作簿
    ws = wb.active  # 获取工作表
    be = BaseExcel(ws)
    be.head = "学生信息收集表"
    be.title = ['姓名', '学号', '年龄']
    be.append(['张三', "1101", 17])
    be.append(['李四', "1102", 18])
    be.append(['王五', "1103", 16])
    be.append(["", "合计", 3], result=True)
    be.execute()
    wb.save(r'..\resource\测试1.xlsx')  # 保存到指定路径
